<html>
<head>
    <title>WEBSQL-SQL执行窗口</title>
    <script src="/static/statics/libs/jquery-1.7.2.js"></script>
    <link rel="stylesheet" href="/static/statics/css/font-awesome.min.css">
    <link rel="stylesheet" href="/static/statics/plugins/layui/css/layui.css" media="all">
    <script src="/static/statics/plugins/layer/layer.js"></script>
    <script src="/static/statics/plugins/layui/layui.js"></script>
    <script src="/static/statics/plugins/ztree/jquery.ztree.all.min.js"></script>
    <link rel="stylesheet" href="/static/statics/plugins/ztree/css/metroStyle/metroStyle.css">
    <link rel="stylesheet" href="/static/common/css/cyStyle.css">
    <link rel="stylesheet" href="/static/common/css/cyType.css">
    <link rel="stylesheet" href="/static/code/codemirror.css">
    <link rel="stylesheet" href="/static/code/show-hint.css" />
    <script src="/static/common/js/pageGrid1.js"></script>
    <script src="/static/common/js/editGrid.js"></script>
    <script src="/static/common/js/whole/cyLayer.js"></script>
    <script src="/static/common/js/whole/common.js"></script>
    <script src="/static/common/js/selectTool.js"></script>
    <script src="/static/code/codemirror.js"></script>
    <script src="/static/code/matchbrackets.js"></script>
    <script src="/static/code/sql.js"></script>
    <script src="/static/code/show-hint.js"></script>
    <script src="/static/code/sql-hint.js"></script>
    <style>
        .sqltxt{
            border:1px solid;
            border-radius:12px;
            box-shadow: 2px 2px 5px #bbb;
            font-size: 20px;
        }
        .sqlTab{
            border:1px solid;
            border-radius:12px;
            box-shadow: 2px 2px 5px #bbb;
            font-size: 20px;
            margin: 5px;
            position: relative;
        }
        .menus{
            border:1px solid;
            border-radius:12px;
            box-shadow: 2px 2px 5px #bbb;
            margin: 5px;
        }
        .layui-table-view{
            margin-top: 10px!important;
        }
        .layui-table-page{
            position: absolute;
            bottom: 0;
            top: 51px;
            left: 0;
            width: 98%;
            padding: 7px 7px 0;
            height: 41px;
            font-size: 12px;
            white-space: nowrap;
        }
        .layui-form-item{
            margin-bottom: 5px;
            margin-top: 5px;
        }
    </style>
</head>
<body>

<div class="menus" style="margin:5px;">
<form class="layui-form">
    <div class="layui-form-item">
        <label class="layui-form-label">数据源:</label>
        <div class="layui-input-inline">
            <div cyType="selectTool" cyProps="url:'/dbSource/dbsourceSqlList',search:'true',filter:'dbtype'"
                 name="dbtype" id="dbtype" value="0" cyProps.tips="请选择数据源" class="layui-input-inline"></div>
        </div>
        <div class="">
            <button class="layui-btn search-btn" id="queryBtn" >
                <i class="fa fa-search">&nbsp;</i>执行F8
            </button>
            <button id="newSqlOpen" onclick="window.open('/dbSource/sqlExecute/sqlPage');return false"  class="layui-btn layui-btn-normal">
                <i class="fa fa-link">&nbsp;</i>新窗口F7
            </button>
            <div class="layui-input-inline">
                <div cyType="selectTool" cyProps="url:'/dbSource/sqlController/sqlTextList',search:'true',filter:'sqlTexts'"
                     name="sqlTexts" id="sqlTexts" cyProps.tips="无已保存的SQL"  value="0" class="layui-input-inline"></div>
            </div>
            <button   id="saveSqlText" class="layui-btn layui-btn-warm">
                <i class="fa fa-save">&nbsp;</i>保存F9
            </button>
            <button id="exportData" class="layui-btn layui-btn-primary">
                <i class="fa fa-file-excel-o">&nbsp;</i>导出
            </button>
        </div>
    </div>
</form>
</div>

<div class="sqltxt" >
    <textarea id='sqlText' style="width: 100%;height: 500px;display:block;" ></textarea>
</div>
<div id="msgDiv" class="layui-row sqlTab" style="display: none;">
    <div style="font-size: 15px;color: #843534;display:block;">
         <span id="msgSpan" style="margin-left: 10px;display:block;width:95%;word-wrap:break-word;white-space:normal;"></span>
    </div>
</div>
<div class="layui-row sqlTab" style="display: none;" id="sqlTableDiv">
    <div class="layui-tab layui-tab-card" lay-filter="sqlTab">
        <ul class="layui-tab-title" id="lists">
        </ul>
        <div class="layui-tab-content" id="listsContent">
        </div>
    </div>
    <div class="layui-form nowrap">
        <table id="sqlTable" lay-filter="test"></table>
    </div>
</div>


<script>
    layui.use(['form','table','element'], function(){
         form = layui.form;
         table = layui.table;
         element = layui.element;
         var source={app: [],version: []};
          t1=0;
          tabId = 1;
          tabData=null;
         var dates =0;
         indexOpen=0;
         resultData = [];
          editor = CodeMirror.fromTextArea(document.getElementById("sqlText"), {
            lineNumbers: true,
            mode: {
                name: "text/x-plsql"
            },
            matchBrackets: true,
            styleActiveLine: true, // 当前行背景高亮
            indentWithTabs: true,
            smartIndent: true,
            lineNumbers: true,
            spellcheck:true,
            extraKeys: {
                'Ctrl': 'autocomplete',
                Tab: function (cm) {
                    var  spaces = Array(cm.getOption('indentUnit') + 1).join(' ');
                    cm.replaceSelection(spaces);
                }
            },
            hintOptions: {
                tables:source,
                completeSingle: false
            }
        });

        form.on('select(sqlTexts)', function (data) {
            var datas = data.value;
            var sqltext = editor.getValue();
            if(sqltext!="" || sqltext!=null){
                datas = sqltext+'\n'+datas+"\n";
            }
            editor.setValue(datas);
        });
        element.on('tab(sqlTab)', function(data){
            tabId = data.index;
        });
        //执行sql
        $("#queryBtn").click(function(){
            $("#msgDiv").show();
            $("#msgSpan").text("");
            dates=0;
            var dbtype1 = $("#dbtype  option:selected").text();
            var sqltext = editor.getValue().trim();
            if(dbtype1=="请选择" || dbtype1==''){
                 Msg.error("请先选择数据源~");
                $("#msgDiv").show();
                $("#msgSpan").text("ERROR:请先选择数据源~");
                return false;
            }
            if(sqltext=="" || sqltext==null){
                 Msg.error("请输入要执行的SQL~");
                $("#msgDiv").show();
                $("#msgSpan").text("ERROR:请输入要执行的SQL~");
                return false;
            }
            indexOpen = Loading.open(0,true);
            t1 = setInterval(function () {
                dates++;
                $("#msgSpan").text("执行时长:"+ dates +"毫秒");
            }, 200);
            var url = "/dbSource/sqlExecute/executeSql";
            var params= {'dataType':dbtype1,'sqlText':sqltext};
            $.ajax({
                type:'POST',
                url:url,
                data:params,
                async:true,
                cache:false,
                success:function(data){
                    if(data.code!="1") {
                        Loading.close(indexOpen);
                        window.clearInterval(t1);
                        Msg.error(data.msg);
                        $("#msgDiv").show();
                        $("#msgSpan").text("ERROR:"+data.msg);
                        $("#sqlTableDiv").hide();
                        return false;
                    }
                    if(data.sqlExecuteType=="SELECT"){
                        resultData=[];
                        resultData = data.dataList;
                        tableInit(table,resultData);
                    }else{
                        Loading.close(indexOpen);
                        window.clearInterval(t1);
                        $("#msgSpan").text("执行条数:"+data.data);
                    }
                },
                error:function (data) {
                    Loading.close(indexOpen);
                    window.clearInterval(t1);
                    Msg.error("执行异常!");
                }
            });

           return false;
        });
    });
    //结果集
    function tableInit(table,data) {
        $(".resultLi").remove();
        $("#sqlTableDiv").hide();
        tabData = [];
        for (var i=0;i<data.length;i++){
            if(data[i].code!=1){
                $("#msgSpan").append("结果集"+(i+1)+"ERROR:"+data[i].msg+" \n ");
                continue;
            }
            if(data[i].data.length==0){
                $("#msgSpan").append("结果集"+(i+1)+"：SQL执行成功,返回结果为空！ \n ");
                continue;
            }
            $("#sqlTableDiv").show();
            var headArr =  Object.keys(data[i].data[0]);
            var head=[];
            for(var key in headArr){
                var tempHead = {field: headArr[key], title: headArr[key],  sort: true};
                head.push(tempHead);
            }
            var elId ="sqlTable"+i;
            $("#lists").append(" <li class='resultLi'>结果集"+(i+1)+"</li>");
            $("#listsContent").append("<div class='layui-tab-item resultLi'><table class='layui-table' id='"+elId+"'></table></div>");
           var  elIds = table.render({
                 elem: '#'+elId
                ,cols: [head]
                ,data:data[i].data
                ,title:'结果集'+(i+1)
                ,loading:true
                ,page:{prev:'上一页',next:'下一页',groups:6}
            });
            tabData[i]=elIds;
        }
        $("#lists li:first").attr("class","layui-this resultLi");
        $("#listsContent div:eq(0)").attr("class","layui-tab-item layui-show resultLi");
        window.clearInterval(t1);
        Loading.close(indexOpen);
        return false;
    }
    //导出
    $("#exportData").click(function () {
        if(tabData==null){
            Msg.error("结果集为空,无法导出！");
            return false;
        }
        if(tabData.length==0){
            Msg.error("结果集为空,无法导出！");
            return false;
        }
        var tempData = tabData[tabId];
        table.exportFile(tempData.config.id,tempData.config.data, 'csv');
        return false;
    });
    //保存SQL文本
    $("#saveSqlText").click(function () {
        var sqltext = editor.getValue();
        if(sqltext.trim()=="" || sqltext.trim()==null){
            Msg.error("SQL语句为空，无法保存");
            return false;
        }
        Alert.prompt('保存SQL语句,请输入标题','3',function(value) {
            var url = "/dbSource/sqlController/saveSqlText";
            var params = {'sqlText':sqltext,'title':value};
            $.ajax({
                type:'POST',
                url:url,
                data:params,
                cache:false,
                async:true,
                success:function(data){
                    Msg.success(data);
                },
                error:function (data) {
                    Msg.error("保存异常!");
                }
            });
        })
        return false;
    });
    //快捷键
    document.onkeydown=function(event){
        var e = event || window.event || arguments.callee.caller.arguments[0];
        if(e && e.keyCode==119){
            $("#queryBtn").trigger('click');
        }
        if(e && e.keyCode==118){
            $("#newSqlOpen").trigger('click');
        }

        if(e && e.keyCode==120){
            $("#saveSqlText").trigger('click');
        }
    };
    </script>
</body>
</html>